const mysql = require('mysql2/promise')
const Router = require('koa-router')
const moment = require('moment')
const Chance = require('chance')

const chance = new Chance

const router = new Router({
  prefix: '/v2'
})

// promise all example
router.get('/promise', function* () {
  const connMall = yield mysql.createConnection({
    host: '122.144.199.196',
    user: 'backup',
    password: 'S8S%2Mnd^tA',
    database: 'pro_yunmall',
  })
  const connSupply = yield mysql.createConnection({
    host: '122.144.199.196',
    user: 'backup',
    password: 'S8S%2Mnd^tA',
    database: 'pro_yunsupply2',
  })
  const rs = yield connMall.execute('SELECT * FROM `yunmall_seller` where id>=50 and id<=100')
  let promiseArr = []
  rs[0].forEach(v => {
    const promise = connSupply.execute(`SELECT userid,username,areaid FROM \`yunsupply_member\` where username=?`, [v.seller_name])
    promiseArr.push(promise)
  })
  const rsrs = yield Promise.all(promiseArr)
  rsrs.forEach(v => {
    console.log(v[0])
  })
  this.body = 'promise'
})

router.get('/import2excel', function* () {
  this.body = 'import mysql data to the excel?'
})

function generateRandomTime() {
  const startDate = '2016-04-20'
  const start = new Date(startDate).getTime() / 1000
  const end = new Date('2016-10-18').getTime() / 1000
  // 时间差的秒数
  const delta = end - start
  const randomNumber = Math.floor(Math.random() * delta) + 1
  return moment(startDate).add(randomNumber, 'seconds').format('YYYY-MM-DD HH:mm:ss')
}

// console.log(chance.cc_type({raw: true}))
router.get('/random-all', function* () {
  const randomDateTime = generateRandomTime()
  console.log(randomDateTime)
  const randomIp = chance.ip()
  console.log(randomIp)
  // 2016-04-20
  // 2016-10-18
  // pro_yunmall -> yunmall_seller -> update create_time area
  // 1 - 110
  // [username]
  // pro_yunsupply2 -> yunsupply_member -> update areaid regip regtime
  this.body = 'random'
})

router.get('/random-create-time', function* () {
  // pro_yunmall -> yunmall_seller -> random create_time
  // SELECT id,seller_name,create_time,province,city,area FROM `yunmall_seller`
  const connMall = yield mysql.createConnection({
    host: '122.144.199.196',
    user: 'backup',
    password: 'S8S%2Mnd^tA',
    database: 'pro_yunmall',
  })
  let rs = yield connMall.execute(`SELECT id,seller_name,create_time,province,city,area FROM \`yunmall_seller\``)
  let pa = [] // promise array
  // rs[0].forEach(v => {
  //   console.log(v, 88, v.create_time.toLocaleString())
  //   const p = connMall.execute(`UPDATE \`yunmall_seller\` set create_time="${generateRandomTime()}" where id=${v.id}`)
  //   pa.push(p)
  // })
  // const data = yield Promise.all(pa)
  // data.forEach(vv => {
  //   console.log(vv, '--vv')
  // })
  this.body = 'random create time'
})

router.get('/random-ip', function* () {
  let output = chance.ip()
  this.body = output
})

router.get('/random-area', function* () {
  let output = 'random area'
  const connMall = yield mysql.createConnection({
    host: '122.144.199.196',
    user: 'backup',
    password: 'S8S%2Mnd^tA',
    database: 'pro_yunmall',
  })
  // get area id array exclude regexp `0$`
  // SELECT * FROM `yunmall_areas` where area_id REGEXP '^[[:digit:]]{5}[1-9]$'
  const rs = yield connMall.execute(`SELECT * FROM \`yunmall_areas\` where area_id REGEXP '^[[:digit:]]{5}[1-9]$'`)
  let areaArr = []
  rs[0].forEach(v => {
    areaArr.push(v.area_id)
  })
  // console.log(chance.pickone(areaArr))
  const one = chance.pickone(areaArr)
  console.log(one, typeof one)
  const two = one.toString().substr(0, 4) + '00'
  // usage parseInt
  const three = two.substr(0, 2) + '0000'
  // usage parseInt
  // int
  // update yunmall_seller set area=?, city=?, province=? where id=?
  // the prevous is the sql
  // one parseInt(two) parseInt(three)
  // todo update the random area id of the
  const sellers = yield connMall.execute('SELECT id,seller_name,create_time,province,city,area FROM `yunmall_seller`')
  let promiseArr = []
  sellers[0].forEach(v => {
    const one = chance.pickone(areaArr)
    console.log(one, typeof one)
    const two = one.toString().substr(0, 4) + '00'
    // usage parseInt
    const three = two.substr(0, 2) + '0000'
    // usage parseInt
    const promise = connMall.execute('UPDATE `yunmall_seller` set area=?,city=?,province=? where id=?', [one, parseInt(two), parseInt(three), v.id])
    promiseArr.push(promise)
  })
  const r = yield Promise.all(promiseArr)
  r.forEach(v => {
    console.log(v[0], '--rrrrr')
  })
  // to here the yunmall_seller random area is ok
  this.body = areaArr
})

router.get('/update-member', function* () {
  // pro_yunsupply2 -> yunsupply_member
  // 1 random regip(varchar) // ok
  // 2 sync regtime(int) and areaid(int)
  let output = 'update member'
  // sync regtime areaid
  // the data source
  const connMall = yield mysql.createConnection({
    host: '122.144.199.196',
    user: 'backup',
    password: 'S8S%2Mnd^tA',
    database: 'pro_yunmall',
  })
  const connSupply = yield mysql.createConnection({
    host: '122.144.199.196',
    user: 'backup',
    password: 'S8S%2Mnd^tA',
    database: 'pro_yunsupply2',
  })
  const dataSource = connMall.execute
  // SELECT id,seller_name,create_time,area FROM `yunmall_seller`
  const sellerRs = yield connMall.execute('SELECT id,seller_name,create_time,area FROM `yunmall_seller`')
  const pa = []
  sellerRs[0].forEach(v => {
    const promise = connSupply.execute('update yunsupply_member set areaid=?,regtime=? where username=?', [v.area, moment(v.create_time).valueOf() / 1000, v.seller_name])
    pa.push(promise)
  })
  const r = yield Promise.all(pa)
  console.log(r, '--r')
  this.body = output
})

router.get('/update-member-random-ip', function* () {
  const connSupply = yield mysql.createConnection({
    host: '122.144.199.196',
    user: 'backup',
    password: 'S8S%2Mnd^tA',
    database: 'pro_yunsupply2',
  })
  // first get all data
  // sql[SELECT userid,username,regip,regtime,areaid FROM `yunsupply_member`]
  const rs = yield connSupply.execute('SELECT userid,username,regip,regtime,areaid FROM `yunsupply_member`')
  let pa = []
  rs[0].forEach(v => {
    // const promise = connSupply.execute('SELECT userid,username,regip,regtime,areaid FROM `yunsupply_member` where username=?', [v.username])
    const promise = connSupply.execute('UPDATE `yunsupply_member` set regip=? where userid=?', [chance.ip(), v.userid])
    pa.push(promise)
  })
  const r = yield Promise.all(pa)
  r.forEach(v => {
    console.log(v, '---verbose')
  })
  this.body = 'update member random ip ok'
})

router.get('/last-test', function* () {
  // mysql connection resource
  const connMall = yield mysql.createConnection({
    host: '122.144.199.196',
    user: 'backup',
    password: 'S8S%2Mnd^tA',
    database: 'pro_yunmall',
  })
  const connSupply = yield mysql.createConnection({
    host: '122.144.199.196',
    user: 'backup',
    password: 'S8S%2Mnd^tA',
    database: 'pro_yunsupply2',
  })
  // start logic
  const mallUserRs = yield connMall.execute('SELECT id,seller_name,create_time,area FROM `yunmall_seller`')
  let pa = [] // promise array to use for Promise.all
  mallUserRs[0].forEach(v => {
    console.log(v, '-single v')
    const promise = connSupply.execute('SELECT userid,username,regip,regtime,areaid FROM `yunsupply_member` where username=?', [v.seller_name])
    pa.push(promise)
  })
  const r = yield Promise.all(pa)
  r.forEach(v => {
    console.log(v[0], 'info')
  })
  this.body = 'last-test'
})

module.exports = router